package server;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import entities.User;

import ocsf.server.ConnectionToClient;
/**
 * Query controllers for the User
 * @author Benny
 *
 */
public class UserQueryHandle 
{
	public mysqlConnection sqlCon;
	public User user;
	/**
	 * Constructor of the object
	 * @param c established connection to the SQL DataBase
	 */
	public UserQueryHandle(mysqlConnection c)
	{
		this.sqlCon=c;
	}
	
	/**
	 * Handling message from client and sending an answer
	 * @param msg received message from a specific client, through the running server
	 * @param client a specific client connected to the running server
	 * @throws SQLException
	 * @throws IOException
	 */
	public void handleMessage (Object msg, ConnectionToClient client) throws SQLException, IOException
	{
		ArrayList<Object> messages=(ArrayList<Object>) msg;
		ArrayList<Object> res=new ArrayList<>();;
		String command = (String) messages.get(1);

		res.add(messages.get(0).toString()); // add role
		res.add(messages.get(1).toString()); // add type
		System.out.println(command);
		//index=2, userID
			
		/**
		 * Query controller for getting all the borrows for a specific userID
		 */
		if(command.equals("ViewBorrowsForUser"))
		{
			Calendar cal = Calendar.getInstance();
			DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String today=sdf.format(cal.getTime());
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			int userID = (int) messages.get(2);
			ResultSet rs = stmt.executeQuery("SELECT * FROM borrows,books,bookcopies WHERE borrows.copyID=bookcopies.copyID AND bookcopies.bookCode=books.bookCode AND borrows.borrowStatus='active' AND borrows.userID='" + userID + "';");
			if(rs.next())
			{
				res.add("OK");
				rs.previous();
				while(rs.next())
				{
					String[] borrow = new String[4];
					String cid=Integer.toString(rs.getInt("copyID"));			
					Date d1 = null,d2 = null;
					String returnDate = rs.getString("returnDate");
					DateFormat retDay=new SimpleDateFormat("yyyy-MM-dd");
					try {
						d1=(Date) retDay.parse(returnDate);
						d2=(Date) retDay.parse(today);
					} catch (ParseException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					borrow[0]=rs.getString("name");
					borrow[1]=cid;
					borrow[2]=returnDate;


					if(d2.compareTo(d1)>0)
					{
						borrow[3]="late";
					}
					else
						borrow[3]="ok";
                    res.add(borrow);
				}
			}
			else
				res.add("Fail");
			rs.close();
			client.sendToClient(res);
		}
			
		/**
		 * Query controller for setting a new email and phone for a specific userID
		 */	
		if(command.equals("UpdateUserCard"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			int userID = (int) messages.get(2);
			ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE userID='" + userID + "';");
			if (rs.next()) {
				Statement stmtUpdt;
		        stmtUpdt = sqlCon.conn.createStatement();
		        String email = messages.get(3).toString();
		        String phone = messages.get(4).toString();
				int result = stmtUpdt.executeUpdate("UPDATE users SET email='"+email+"', phone='"+phone+"' WHERE userID='"+ userID + "';");
				if(result>0)
				{
					res.add("OK");
	                user= new User(rs.getInt("userID"),rs.getInt("id"), rs.getString("username"), rs.getString("password"), phone, email,
	                           rs.getString("role"), rs.getString("status"), rs.getString("cardStatus"));
	                res.add((Object)user);
				}
				else res.add("Fail");	
				}
			else res.add("Fail");
				
				rs.close();
				
			client.sendToClient(res);	
		} 
		/**
		 * Query controller for setting a new email, phone and password for a specific userID
		 */	
		if(command.equals("UpdateCardPass"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			int userID = (int) messages.get(2);
			String pass = (String) messages.get(5);
			ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE userID='" + userID + "' AND password='" + pass + "';");
			if (rs.next()) {
				Statement stmtUpdt;
		        stmtUpdt = sqlCon.conn.createStatement();
		        String email = messages.get(3).toString();
		        String phone = messages.get(4).toString();
				int result = stmtUpdt.executeUpdate("UPDATE users SET email='"+email+"', phone='"+phone+"', password='"+messages.get(6).toString()+"' WHERE userID='"+ userID + "' AND password='"+pass+"';");
				if(messages.get(7).equals(messages.get(6)))
				{
				if(result>0)
				{
					res.add("OK");
	                user= new User(rs.getInt("userID"),rs.getInt("id"), rs.getString("username"), rs.getString("password"), phone, email,
	                           rs.getString("role"), rs.getString("status"), rs.getString("cardStatus"));
	                res.add((Object)user);
				}
				else res.add("Fail");	
				}
				else res.add("PassError");
			}
			else res.add("Fail");

				rs.close();
				
			client.sendToClient(res);	
		} 
		/**
		 * Query controller for getting the return date for a specific copy
		 */	
		if(command.equals("ShowReturnDate"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			int copyID = (int) messages.get(2);
			ResultSet rs = stmt.executeQuery("SELECT * FROM borrows WHERE copyID='" + copyID + "' AND borrowStatus='active';");
			if(rs.next())
			{
				String returnDate=rs.getString("returnDate");
				res.add("OK");
				res.add(returnDate);
			}
			else
			{
				res.add("Fail");
			}
			rs.close();
			client.sendToClient(res);	
		}
		
		/**
		 * Query controller for setting a new return date for a specific user and his copy 
		 */	
		if(command.equals("ExtendBorrow"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			int userID=(int)messages.get(2);
			int copyID=(int)messages.get(3);


			Calendar cal = Calendar.getInstance();
			DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String today=sdf.format(cal.getTime());
			ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE cardStatus='ok' AND userID='" + userID + "';");
			if(rs.next())
			{
				ResultSet rs1=stmt.executeQuery("SELECT * FROM bookcopies WHERE ordered='no' AND copyID='" + copyID + "';");
				int bookCode;
				String rated;
				if(rs1.next())
				{
					bookCode=rs1.getInt("bookCode");
					rs1=stmt.executeQuery("SELECT * FROM borrows WHERE borrowStatus='active' AND copyID='" + copyID + "';");
					if(rs1.next())
					{
						Calendar d3 = Calendar.getInstance();
                        Calendar d4 = Calendar.getInstance();
                        d3.setTime(rs.getDate("borrowDate")) ;
                        d4.setTime(rs.getDate("actualReturnDate"));
                        long days=(d4.getTime().getTime() - d3.getTime().getTime()) / (24 * 3600 * 1000);
						Date d1 = null,d2 = null;
						String returnDate = rs1.getString("returnDate");
						DateFormat retDay=new SimpleDateFormat("yyyy-MM-dd");
						try {
							d1=(Date) retDay.parse(returnDate);
							d2=(Date) retDay.parse(today);
						} catch (ParseException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
						if(d2.compareTo(d1)<=0)
						{
							Statement stmtUpdt = sqlCon.conn.createStatement();
							rs1=stmt.executeQuery("SELECT * FROM books WHERE bookCode='"+ bookCode+"';");
							rs1.next();
							rated=rs1.getString("Rated");
							if(rated.equals("yes"))
							{
									res.add("Fail");
							}
							else if(rated.equals("no") && days<12)
							{
								cal.add(Calendar.DATE, 3);
								String isRated=sdf.format(cal.getTime());
								int update= stmtUpdt.executeUpdate("UPDATE borrows SET returnDate='" + isRated+ "',actualReturnDate='"+ isRated +  "' WHERE copyID='"+ copyID + "'AND borrowStatus='active';");
								if(update>0)
								{
									res.add("OK");
									res.add(isRated);
								}
								else
									res.add("Fail");
							}	
						}
						else
							res.add("Fail");	
					}
					else
						res.add("Fail");
				}
				else
					res.add("Fail");
				rs1.close();
			}
			else
				res.add("Fail");
			rs.close();
			client.sendToClient(res);
		}
		/**
		 * Query controller for getting all the borrows comitted by a specific userID
		 */	
		if(command.equals("RecentActivity"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				res.add("Error");
				e.printStackTrace();
			}
			int userID = (int) messages.get(2);
			ResultSet rs = stmt.executeQuery("SELECT borrows.borrowID, borrows.copyID, borrows.borrowDate, borrows.returnDate, borrows.actualReturnDate, books.name FROM borrows, books, bookcopies WHERE borrows.userID='" + userID + "' AND borrows.copyID=bookcopies.copyID AND bookcopies.bookCode=books.bookCode ;");
            if (rs.next()) {
                res.add("OK");
                rs.previous();

                while (rs.next()) {
               	 String[] activity = new String[6];
               	 String copyID = Integer.toString(rs.getInt(2));
               	 String borrowID = Integer.toString(rs.getInt(1));                   	 
               	 
               	 	activity[0]=borrowID;
               	 	activity[1]=copyID;
               	 	activity[2]=rs.getString(3);
               	 	activity[3]=rs.getString(4);
               	 	activity[4]=rs.getString(5);
               	 	activity[5]=rs.getString(6);
                    res.add(activity);
                    
                    System.out.println(activity.toString());
               }

            } 
            else res.add("Fail");

            rs.close();
        
			client.sendToClient(res);	
		} 
		/**
		 * Query controller for placing an order for a specific copy by a specific userID
		 */	
		if(command.equals("Order"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				res.add("Error");
				e.printStackTrace();
			}
			int userID = (int) messages.get(2);
			int copyID = (int) messages.get(3);
			Calendar cal = Calendar.getInstance();
			Calendar calNext = Calendar.getInstance();
			DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd H-m-s");
			String today=sdf.format(cal.getTime());
			ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE userID='" + userID + "'AND cardStatus='ok';");
			if(rs.next())
			{
				System.out.println("user status is ok");
				rs = stmt.executeQuery("SELECT * FROM bookcopies WHERE copyID='" + copyID + "';");
				if(rs.next())
				{
					System.out.println("server found the requested copyID");
					String isBorrowed = rs.getString("inBorrow");
					if(isBorrowed.equals("yes"))
					{
						System.out.println("copyID is borrowed");
						Statement stmtUpdt = sqlCon.conn.createStatement();
						int update;
						String isOrdered = rs.getString("ordered");
						if(isOrdered.equals("no"))
						{					
							System.out.println("copyID is not ordered");
							rs = stmt.executeQuery("SELECT * FROM orders WHERE copyOrderID='" + copyID + "'AND userOrderID='"+ userID + "' AND orderStatus='active';");
							if(!rs.next())
							{
								System.out.println("order for this copyID and userID is inactive");
								update = stmtUpdt.executeUpdate("UPDATE bookcopies SET ordered='yes' WHERE copyID='"+ copyID + "';");
								if(update>0)
								{
									System.out.println("update bookcopies, ordered=yes");
									update =stmtUpdt.executeUpdate("INSERT INTO orders (userOrderID,copyOrderID,orderStatus,orderDate,inLibrary) VALUES('"+userID+"','" + copyID + "','active','"+today+"','no');");
									if(update>0)
									{
										System.out.println("insert new order to table");
										res.add("OK");
									}
									else
										res.add("Fail");
								}
								else
									res.add("Fail");
							}
							else
							{
								System.out.println("order for this copyID and userID is active");
								res.add("Fail");
							}
						}
						else  //isOrdered.equals("yes")
						{
							System.out.println("copyID is already ordered");
							rs = stmt.executeQuery("SELECT * FROM orders WHERE copyOrderID='" + copyID + "'AND userOrderID='"+ userID + "'AND orderStatus='active';");
							if(!rs.next())
							{
								update = stmtUpdt.executeUpdate("INSERT INTO orders (userOrderID,copyOrderID,orderStatus,orderDate,inLibrary) VALUES('"+userID+"','" + copyID + "','active','"+today+"','no');");
								if(update>0)
								{
									System.out.println("insert new order to table");
									res.add("OK");
								}
								else
									res.add("Fail");
							}
							else res.add("Fail");
						}
					}
					else
						res.add("Fail");
						
				}
				else
					res.add("Fail");
			}
			else
				res.add("Fail");
			rs.close();
			client.sendToClient(res);	
		}
		if(command.equals("viewErrors"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				res.add("Error");
				e.printStackTrace();
			}
			int userID=(int) messages.get(2);
			ResultSet rs=stmt.executeQuery("SELECT * FROM errors,borrows WHERE errors.borrowErrorID=borrows.borrowID AND borrows.userID='"+userID+"';");
			if(rs.next())
			{
				res.add("OK");
				rs.previous();
				while(rs.next())
				{
					String [] errors=new String[4];
					errors[0]=Integer.toString(rs.getInt("errorID"));
					errors[1]=rs.getString("errorStatus");
					errors[2]=rs.getString("errorDate");
					errors[3]=Integer.toString(rs.getInt("borrowErrorID"));
					res.add(errors);		
				}
			}
			else
				res.add("Fail");
			rs.close();
			client.sendToClient(res);
		}
		
		if(command.equals("viewOrders"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				res.add("Error");
				e.printStackTrace();
			}
			int userID=(int) messages.get(2);
			ResultSet rs=stmt.executeQuery("SELECT * FROM orders WHERE userOrderID='"+userID+"';");
			if(rs.next())
			{
				res.add("OK");
				rs.previous();
				while(rs.next())
				{
					String [] orders=new String[4];
					orders[0]=Integer.toString(rs.getInt("orderID"));
					orders[1]=rs.getString("orderStatus");
					orders[2]=rs.getString("orderDate");
					orders[3]=Integer.toString(rs.getInt("copyOrderID"));
					res.add(orders);		
				}
			}
			else
				res.add("Fail");
			rs.close();
			client.sendToClient(res);
		}
	}
}